package bupt.party.online.dao.impl;

import java.sql.Timestamp;
import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.Session;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.ProjectionList;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import bupt.party.online.dao.ILearnCenterDao;
import bupt.party.online.model.LearnCenter;

@SuppressWarnings("unchecked")
public class LearnCenterDaoImpl extends HibernateDaoSupport implements ILearnCenterDao  {
	
	public void saveLearnCenter(LearnCenter learnCenter) {
		this.getHibernateTemplate().save(learnCenter);
	}

	public void deleteLearnCenter(LearnCenter learnCenter) {
		this.getHibernateTemplate().update(learnCenter);
	}

	public void updateLearnCenter(LearnCenter learnCenter) {
		this.getHibernateTemplate().update(learnCenter);
	}

	@SuppressWarnings("rawtypes")
	public List findLearnCenterByTitle(String title, Integer tag, int first,
			int pageSize) {
		
		DetachedCriteria criteria = DetachedCriteria.forClass(LearnCenter.class);
		criteria.add(Restrictions.eq("tag", tag));      //设置tag查找标记
		criteria.add(Restrictions.eq("isDelete", 0));   //设置未被删除标记
		criteria.addOrder(Order.desc("posterTime"));    //设置按照时间降序排列
		if(title != null && title.trim().length() > 0) {
			criteria.add(Restrictions.like("title", "%" + title + "%"));  //设置模糊查询的title条件
		}
		
		//设置需要查找的字段，使用投影来实现
		ProjectionList projectionList = Projections.projectionList();
		projectionList.add(Projections.property("id"));
		projectionList.add(Projections.property("title"));
		projectionList.add(Projections.property("posterNo"));
		projectionList.add(Projections.property("posterTime"));
		projectionList.add(Projections.property("visitsCount"));
		
		criteria.setProjection(projectionList);
		
		return this.getHibernateTemplate().findByCriteria(criteria, first, pageSize);
	}

	public int findLearnCenterCountByTitle(final String title, final Integer tag) {
		Criteria criteria = this.getSession().createCriteria(LearnCenter.class);
		criteria.add(Restrictions.eq("tag", tag));
		criteria.add(Restrictions.eq("isDelete", 0));   //设置未被删除标记
		if(title != null && title.trim().length() > 0) {
			criteria.add(Restrictions.like("title", "%" + title +"%"));
		}
		//返回查找的数量
		return Integer.parseInt(criteria.setProjection(Projections.rowCount()).uniqueResult().toString());
	}
	
	public LearnCenter findLearnCenterById(Integer id) {
		 LearnCenter learnCenter = null;
		 StringBuffer sqlStringBuffer = new StringBuffer(); 
		 sqlStringBuffer.append("select center.id, center.title, center.poster_no, center.poster_time," +
		   		"center.visits_count, admin.admin_name, center.content from admin admin, learn_center center " + 
		         "where center.is_delete=0 and center.poster_no=admin.admin_no and center.id=" + id.intValue());
		 List list = this.getSession().createSQLQuery(sqlStringBuffer.toString()).list();
		 if(list != null && list.size() > 0) {
			 Object[] obj = (Object[])list.get(0);
			 learnCenter = new LearnCenter();
			 learnCenter.setId((Integer)obj[0]);
		     learnCenter.setTitle((String)obj[1]);
		     learnCenter.setPosterNo((String)obj[2]);
			 learnCenter.setPosterTime((Timestamp)obj[3]);
			 learnCenter.setVisitsCount((Integer)obj[4]);
			 learnCenter.setPosterName((String)obj[5]);
			 learnCenter.setContent((String)obj[6]);
		 }
		 return learnCenter;
	}

	public void addLearnCenterVisitsCount(Integer id) {
		String sqlString = "update learn_center set visits_count=visits_count+1 where id='"+id+"'";
		this.getSession().createSQLQuery(sqlString).executeUpdate();
	}

	public void deleteLearnCenterById(Integer id) {
		String updateString = "update LearnCenter set isDelete=1 where id=" + id;
		this.getHibernateTemplate().bulkUpdate(updateString);
	}

	@SuppressWarnings("rawtypes")
	public List findAdminLearnCenterByTitle(String title, Integer tag,
			final int first, final int pageSize) {
		
		   StringBuffer sqlStringBuffer = new StringBuffer(); 
		   sqlStringBuffer.append("select center.id, center.title, center.poster_no, center.poster_time, " +
		   		"center.visits_count, admin.admin_name from admin admin, learn_center center " + 
		         "where center.is_delete=0 and center.poster_no=admin.admin_no ");
		   
		   if(title != null && title.trim().length() > 0) {
			   sqlStringBuffer.append(" and center.title like '%" + title.trim() + "%' ");
		   }
		   if(tag != null) {
			   sqlStringBuffer.append(" and center.tag=" + tag.intValue());   
		   }
		   sqlStringBuffer.append(" order by center.poster_time desc");  //设置按照发布时间降序排列
		   
		   String queryString =  sqlStringBuffer.toString();
		  
		   return this.getSession().createSQLQuery(queryString).setFirstResult(first).setMaxResults(pageSize).list();
	}

	@SuppressWarnings("rawtypes")
	@Override
	public List getLatest4Notices() {
		DetachedCriteria criteria = DetachedCriteria.forClass(LearnCenter.class);
		criteria.add(Restrictions.eq("isDelete", 0));   //设置未被删除标记
		criteria.addOrder(Order.desc("posterTime"));    //设置按照时间降序排列
		criteria.add(Restrictions.eq("tag", 5));
		return this.getHibernateTemplate().findByCriteria(criteria, 0, 6);
	}
	
}
